package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import bean.McTypeBean;
import dao.inf.IMcTypeDAOInf;
import db.OracleDB;

public class McTypeDAOImpl implements IMcTypeDAOInf {
	@Override
	public boolean insertMcType(McTypeBean typeBean) {
		int row = 0;
		Connection conn = OracleDB.getConn();
		PreparedStatement pstmt = null;
		String sql = "insert into t_mc_type values(?,?,?)";
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, typeBean.getNid());
			pstmt.setString(2, typeBean.getSname());
			pstmt.setInt(3, typeBean.getNpid());
			row = pstmt.executeUpdate();
			if (row > 0) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			OracleDB.close(rs, pstmt, conn);
		}
		return false;
	}

	@Override
	public boolean deleteMcType(McTypeBean typeBean) {
		int row = 0;
		Connection conn = OracleDB.getConn();
		PreparedStatement pstmt = null;
		String sql = "delete from t_mc_type where nid=?";
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, typeBean.getNid());
			row = pstmt.executeUpdate();
			if (row > 0) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			OracleDB.close(rs, pstmt, conn);
		}
		return false;
	}

	@Override
	public boolean updateMcType(McTypeBean typeBean) {
		int row = 0;
		Connection conn = OracleDB.getConn();
		PreparedStatement pstmt = null;
		String sql = "update t_mc_type set sname=?,npid=? where nid=?";
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, typeBean.getSname());
			pstmt.setInt(2, typeBean.getNpid());
			pstmt.setInt(3, typeBean.getNid());
			row = pstmt.executeUpdate();
			if (row > 0) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			OracleDB.close(rs, pstmt, conn);
		}

		return false;
	}

	@Override
	public List<McTypeBean> queryByNpid(int npid) {
		List<McTypeBean> allTypes = new ArrayList<McTypeBean>();

		Connection conn = OracleDB.getConn();
		PreparedStatement pstmt = null;
		String sql = "select * from t_mc_type where npid=?";
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, npid);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				McTypeBean typeBean = new McTypeBean();
				typeBean.setNid(rs.getInt(1));
				typeBean.setSname(rs.getString(2));
				typeBean.setNpid(npid);
				allTypes.add(typeBean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			OracleDB.close(rs, pstmt, conn);
		}
		return allTypes;
	}

	@Override
	public McTypeBean queryMcType(int nid) {

		Connection conn = OracleDB.getConn();
		PreparedStatement pstmt = null;
		String sql = "select * from t_mc_type where nid=?";
		ResultSet rs = null;
		McTypeBean typeBean = new McTypeBean();
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, nid);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				typeBean.setNid(nid);
				typeBean.setSname(rs.getString(2));
				typeBean.setNpid(rs.getInt(3));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			OracleDB.close(rs, pstmt, conn);
		}
		return typeBean;
	}
	
	@Override
	public int queryMaxNid(){
		Connection conn = OracleDB.getConn();
		PreparedStatement pstmt = null;
		String sql = " select max(nid) from t_mc_type";
		ResultSet rs = null;
		int max = 1;
		try {
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				System.out.println(rs.getInt(1));
				max = rs.getInt(1)+1;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			OracleDB.close(rs, pstmt, conn);
		}
		System.out.println("max="+max);
		return max;
	}
	@Override
	public List<McTypeBean> queryBySname(String sname) {
		List<McTypeBean> allTypes = new ArrayList<McTypeBean>();

		Connection conn = OracleDB.getConn();
		PreparedStatement pstmt = null;
		String sql = "select * from t_mc_type where sname like %?%";
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, sname);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				McTypeBean typeBean = new McTypeBean();
				typeBean.setNid(rs.getInt(1));
				typeBean.setSname(rs.getString(2));
				typeBean.setNpid(rs.getInt(3));
				allTypes.add(typeBean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			OracleDB.close(rs, pstmt, conn);
		}
		return allTypes;
	}
}
